package com.crm.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.crm.entity.ClientUser;
import com.crm.entity.Clientdet;
import com.crm.util.ApplicationContext;


public class ClientdetDAO {
	protected final Log logger = LogFactory.getLog(getClass());
	
	public  int insert(Clientdet cl)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			session.saveOrUpdate(cl);
			tx.commit();
			return 0;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		   //  session.close();
		}
			}
	public  int update(Clientdet c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			session.saveOrUpdate(c);
			tx.commit();
			return 0;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  int delete(Clientdet c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			session.delete(c);
			tx.commit();
			return 0;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  List<Clientdet> findAll(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<Clientdet> custObjs=new ArrayList<Clientdet>();
			 List objs=null;
			Query query=session.createQuery("from Clientdet where business_id=:busid");
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((Clientdet)o);
			}
			return custObjs;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  Clientdet findById(int i,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Clientdet c=(Clientdet)session.load(Clientdet.class, i);
			return c;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  Clientdet getCliOrg(int busid,String orgname)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qurystr="From Clientdet  where business_id=:busid and  organisation=:oname";
			Query qry=session.createQuery(qurystr);
			qry.setString("oname", orgname);
			qry.setInteger("busid", busid);
			Object qryresult=qry.uniqueResult();
			Clientdet cli=(Clientdet)qryresult;
			return cli;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	
	public  Clientdet getClient(int busid,String cliname)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Clientdet where business_id=:busid and organisation=:cliname";
			Query query=session.createQuery(querystr);
			query.setString("cliname", cliname);
			query.setInteger("busid", busid);
			Object queryresult=query.uniqueResult();
			Clientdet c=(Clientdet)queryresult;
			return c;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  String clientCheck(int busid,String companyName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query qry=session.createQuery("from Clientdet where business_id=:busid  and shrtName=:name");
			logger.info("the query is"+qry);
			qry.setString("name",companyName);
			qry.setInteger("busid",busid);
			logger.info("Name In DAO"+companyName);
			Object obj=qry.uniqueResult();
			Clientdet c=(Clientdet)obj;
			try{
				if(c.getShrtname()==null|| c.getShrtname()==""){
				return "true";
				}
			}catch(NullPointerException e){
				logger.info("the client name is In Null pointer+");
				return "true";
			}
			logger.info("the client name is +");
			return "false";
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  String clientCheckwithID(int busid,String companyName,int id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query qry=session.createQuery("from Clientdet where business_id=:busid  and shrtName=:name and id=:id");
			logger.info("the query is"+qry);
			qry.setString("name",companyName);
			qry.setInteger("busid",busid);
			qry.setInteger("id", id);
			logger.info("Name In DAO"+companyName);
			Object obj=qry.uniqueResult();
			Clientdet c=(Clientdet)obj;
			try{
				if(c.getShrtname()==null|| c.getShrtname()==""){
				return "true";
				}
			}catch(NullPointerException e){
				logger.info("the client name is In Null pointer+");
				return "true";
			}
			logger.info("the client name is +");
			return "false";
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  Clientdet getClientByid(int id,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Clientdet where id=:idno and business_id=:busid";
			Query query=session.createQuery(querystr);
			query.setInteger("idno", id);
			query.setInteger("busid", busid);
			Object queryresult=query.uniqueResult();
			Clientdet c=(Clientdet)queryresult;
			return c;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  List<ClientUser> getUser(int busid,int id)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From ClientUser where business_id=:busid and companyid=:cliid";
			Query qry=session.createQuery(qtr);
			qry.setInteger("cliid", id);
			qry.setInteger("busid", busid);
			List<ClientUser> qresult=qry.list();
			return qresult;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  List<Clientdet> getAllCli(int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From Clientdet where business_id=:busid and is_deleted!=1";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid",busid);
			List<Clientdet> qresult=qry.list();
			return qresult;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  void dropusers(int busid,int compid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			String qtr="delete from ClientUser where business_id=:busid and companyid=:id";
			Query qry=session.createQuery(qtr);
			qry.setInteger("id", compid);	
			qry.setInteger("busid", busid);	
			qry.executeUpdate();
			tx.commit();
			return ;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  void deleteCli(int cliid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			String qtr="Update Clientdet set is_deleted=1 where id=:id and business_id=:busid";
			Query qry=session.createQuery(qtr);
			qry.setInteger("id", cliid);	
			qry.setInteger("busid", busid);	
			qry.executeUpdate();
			tx.commit();
			return ;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  String getDiv(int busid,String cliname) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="from Clientdet where business_id=:id and organisation=:organisation";
			Query query=session.createQuery(querystr);
			logger.info(query);
			query.setString("organisation", cliname);
			query.setInteger("id",busid);
			Object queryresult=query.uniqueResult();
			if(queryresult!=null){
			Clientdet o= (Clientdet) queryresult;
			String div=o.getDivision();
			logger.info(div);
			return div;
			}else {
				return "";
			}
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  String getGroups(int busid,String cliname) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="from Clientdet where business_id=:id and organisation=:organisation";
			Query query=session.createQuery(querystr);
			logger.info(query);
			query.setString("organisation", cliname);
			query.setInteger("id",busid);
			Object queryresult=query.uniqueResult();
			if(queryresult!=null){
				Clientdet o= (Clientdet) queryresult;
				String div=o.getGroup();
				logger.info(div);
				return div;
			}else {
				return "";
			}
		}
		catch (Exception ex) {
			throw ex;
		}
		finally {
			session.close();
		}
	}
	public  Object getAddr(int cliid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From Clientdet where id=:cliid and business_id=:busid";
			Query qry=session.createQuery(qtr);
			qry.setInteger("cliid", cliid);	
			qry.setInteger("busid", busid);	
			Object o=qry.uniqueResult();
			return o;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public Clientdet getLastRow() throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From Clientdet";
			Query qry=session.createQuery(qtr);
			List<Object> o=qry.list();
			Clientdet c=new Clientdet();
			for(int i=0;i<o.size();i++){
				c=(Clientdet) o.get(i);
			}
			return c;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public int insertUser(ClientUser cliUser) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			session.saveOrUpdate(cliUser);
			tx.commit();
			return 0;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public List<Clientdet> getorg(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected()+" is open "+session.isOpen());
		
		try {
			String qtr="From Clientdet where business_id=:busid and is_deleted!=1";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid", busid);	
			List<Clientdet> qr=qry.list();
			return qr;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public Clientdet getShrtName(int busId, String org) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From Clientdet where business_id=:busid and organisation=:org";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid", busId);
			qry.setString("org", org);
			Object o=qry.uniqueResult();
			Clientdet cli=(Clientdet)o;
			return cli;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public ClientUser findUserDet(String user, int clientId) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String queryString="from ClientUser where username=:user and companyid=:clientId";
			Query query=session.createQuery(queryString);
			query.setString("user", user);
			query.setInteger("clientId", clientId);
			ClientUser clu=(ClientUser)query.uniqueResult();
			return clu;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	
	public  Clientdet Divisions(String org,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="from Clientdet where organisation=:organisation and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     logger.info(query);
		     String organisation=org;
		     query.setString("organisation", organisation);
		     Object queryresult=query.uniqueResult();
		     Clientdet o= (Clientdet) queryresult;
		     logger.info(o);
		     return o;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	public  List<Object> getUser(int busid,String org,String div)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("In Get User");
		     String query="Select username From ClientUser where companyid=(Select id From Clientdet where business_id=:id and organisation=:org) and user_group=:div";
		     Query qr=session.createQuery(query);
			 qr.setInteger("id", busid);
			 qr.setString("org", org);
			 qr.setString("div", div);
			 List<Object> qresult=qr.list();
			 return qresult;
			
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	
	public  String getcli(String st,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		     String querystr="From Clientdet cli where cli.organisation like '%st%' and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setInteger("busid", busid);
		     List<Object> names=(List<Object>) query.list();
		     String strr="";
		     for(Object ob:names){
		    	 Clientdet cli=(Clientdet)ob;
		     }
		     return strr;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}
	
	
	/*public  String getCliShrtName(String orgname,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		System.out.println("Session is conected "+ session.isConnected());
		try {
		     String querystr="From Clientdet  where organisation=:orgname and business_id=:busid";
		     Query query=session.createQuery(querystr);
		     query.setString("orgname", orgname);
		     query.setInteger("busid", busid);
		     Object queryresult=query.uniqueResult();
		     Clientdet cli=(Clientdet)queryresult;
		     String shrtname=cli.getShrtname();
		     return shrtname;
		}
		catch (Exception e) {
		     throw e;
		}
		finally {
		     session.close();
		}
	}*/
	
}
